package com.wn.EDI.impl;

import com.wn.EDI.bean.BoxIds;
import com.wn.EDI.bean.DatEntityMo;
import com.wn.EDI.bean.DatEntityMt;
import com.wn.EDI.bean.DatEntityMth;
import com.wn.EDI.dao.DatDumpDao;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;

@Repository
public class DatDumpDaoImpl extends HibernateDaoSupport
  implements DatDumpDao
{
  @Autowired
  public void setSessionFactory0(SessionFactory sessionFactory)
  {
    super.setSessionFactory(sessionFactory);
  }

  public List<BoxIds> searchBoxIdDistinct(String id, String internal, String cust) {
    List list = new ArrayList();
    StringBuffer sql = new StringBuffer(
      "select rb.BOX_ID boxId, rb.MDL_TYP_FK spec, rb.WO_ID_FK internal, rb.PRD_QTY qty, rb.MDL_ID_FK mdl, rb.BOX_STAT stat, rw.CUS_ID_FK cus from RET_BOX rb,RET_WO rw where (rb.BOX_STAT ='SHIP' or rb.BOX_STAT ='SHTC') and rb.WO_ID_FK = rw.WO_ID ");

    id = id.trim().replace("[\\\\/:*?\"'<>|]", "");
    internal = internal.trim().replace("[\\\\/:*?\"'<>|]", "");
    cust = cust.trim().replace("[\\\\/:*?\"'<>|]", "");

    if (!id.equals("")) {
      sql.append("AND rb.BOX_ID like '%" + id + "%' ");
    }
    if (!internal.equals("")) {
      sql.append("AND rb.WO_ID_FK LIKE '%" + internal + "%' ");
    }
    if (!cust.equals("")) {
      sql.append("AND rw.CUS_ID_FK='" + cust + "'");
    }
    Query q = getSession().createSQLQuery(sql.toString()).addScalar("boxId", Hibernate.STRING)
      .addScalar("spec", Hibernate.STRING).addScalar("internal", Hibernate.STRING)
      .addScalar("qty", Hibernate.STRING).addScalar("mdl", Hibernate.STRING)
      .addScalar("stat", Hibernate.STRING).addScalar("cus", Hibernate.STRING)
      .setResultTransformer(Transformers.aliasToBean(BoxIds.class));

    if (q.list().size() > 0) {
      list = q.list();
      releaseSession(getSession());
      return list;
    }
    releaseSession(getSession());
    return null;
  }

  public List<DatEntityMo> findByIdModeOne(String id) {
    List list = new ArrayList();
    StringBuffer sql = new StringBuffer(
      "SELECT rpi.PRD_SEQ_ID panelId, rpi.BOX_ID_FK inboxId, bl.LAYOT_ID dpartNo, rb.ACT_CMP_TIMESTAMP inboxIdCfgTime, rpi.BOX_ID_FK outboxId, rb.ACT_CMP_TIMESTAMP outboxIdCfgTime, bl.X_AXIS_CNT xAxisCnt, bl.Y_AXIS_CNT yAxisCnt ");

    StringBuffer sql2 = new StringBuffer(
      "FROM RET_PRD_INFO rpi LEFT OUTER JOIN RET_BOX rb ON rpi.BOX_ID_FK = rb.BOX_ID LEFT OUTER JOIN BIS_MDL_DEF bmd ON rpi.MDL_ID_FK = bmd.MDL_ID LEFT OUTER JOIN BIS_LAYOT bl ON bmd.LAYOT_ID_FK = bl.LAYOT_ID WHERE rpi.BOX_ID_FK = '" + 
      id + "'");
    sql.append(sql2);
    Query q = getSession().createSQLQuery(sql.toString()).addScalar("panelId", Hibernate.STRING)
      .addScalar("inboxId", Hibernate.STRING).addScalar("dpartNo", Hibernate.STRING)
      .addScalar("inboxIdCfgTime", Hibernate.STRING).addScalar("outboxId", Hibernate.STRING)
      .addScalar("outboxIdCfgTime", Hibernate.STRING).addScalar("xAxisCnt", Hibernate.STRING)
      .addScalar("yAxisCnt", Hibernate.STRING)
      .setResultTransformer(Transformers.aliasToBean(DatEntityMo.class));
    
    if (q.list().size() > 0) {
      list = q.list();
      ((DatEntityMo)list.get(0)).setIbxCutCnt(count(sql2));
      releaseSession(getSession());
      return list;
    }
    releaseSession(getSession());
    return null;
  }

  public List<DatEntityMt> findByIdModeTwo(String id) {
    List list = new ArrayList();
    StringBuffer sql = new StringBuffer("SELECT rpi.PRD_SEQ_ID panelId, rb.SHIP_BOX_ID inboxId, rb.PALLET_ID_FK outboxId,rw.cus_mtrl_id_fk mtrl,rw.mdl_cate_fk cate,rw.mdl_id_fk mdl,rw.fm_mdl_id_fk fm,rw.cut_mdl_id_fk cut ");
    StringBuffer sql2 = new StringBuffer("FROM RET_PRD_INFO rpi,RET_BOX rb,RET_WO rw WHERE rpi.BOX_ID_FK='" + id + "' AND rpi.BOX_ID_FK = rb.BOX_ID AND rb.WO_ID_FK = rw.WO_ID");
    sql.append(sql2);
    try {
    	Query q = this.getSession().createSQLQuery(sql.toString()).
    			addScalar("panelId", Hibernate.STRING).addScalar("inboxId", Hibernate.STRING).
    			addScalar("outboxId", Hibernate.STRING).addScalar("mdl", Hibernate.STRING).addScalar("mtrl", Hibernate.STRING).
    			addScalar("cate", Hibernate.STRING).addScalar("fm", Hibernate.STRING).addScalar("cut", Hibernate.STRING).
    			setResultTransformer(Transformers.aliasToBean(DatEntityMt.class));
      if (q.list().size() > 0) {
        list = q.list();
        ((DatEntityMt)list.get(0)).setPnlCnt(count(sql2));
        releaseSession(getSession());
        return list;
      }
      return null;
    } catch (Exception e) {
      e.printStackTrace();
      releaseSession(getSession());
    }
    return null;
  }

  public List<DatEntityMth> findByIdModeThree(String id) {
    List list = new ArrayList();
    StringBuffer sql = new StringBuffer(
      "select rp.PRD_SEQ_ID AS glsId,rp.FAB_SN AS fabSn,IFNULL(DATE_FORMAT(rp.SHIPPING_TIMESTAMP,'%Y%m%d'),'') AS shipDate, rb.SHIP_BOX_ID AS ppboxId,rp.PRD_THICKNESS AS thickness,"
      + "rp.LOT_ID AS lotId,rp.MTRL_PROD_ID_FK AS product,IFNULL(rp.MTRL_SLOT_NO_FK,rp.SLOT_NO) AS slotNo, rp.prd_grade AS reasonCode,rw.T_THICKNESS AS t_thickness, "
      + "rw.C_THICKNESS AS c_thickness, rw.TH_MDL_ID_FK AS jbproduct,  rw.FM_MDL_ID_FK AS dmproduct, rw.CUT_MDL_ID_FK AS cutproduct, rb.EXT_8 AS realboxid ");

    StringBuffer sql2 = new StringBuffer("FROM RET_PRD_INFO rp,RET_BOX rb,RET_WO rw WHERE rp.BOX_ID_FK='" + id + 
      "' AND rp.BOX_ID_FK = rb.BOX_ID AND rp.WO_ID_FK = rw.WO_ID" + 
      " order by CAST(IFNULL(rp.MTRL_SLOT_NO_FK,rp.SLOT_NO) AS SIGNED) asc");
    sql.append(sql2);
    try {
      Query q = getSession().createSQLQuery(sql.toString()).addScalar("glsId", Hibernate.STRING).addScalar("fabSn", Hibernate.STRING)
        .addScalar("shipDate", Hibernate.STRING).addScalar("ppboxId", Hibernate.STRING)
        .addScalar("thickness", Hibernate.STRING).addScalar("lotId", Hibernate.STRING)
        .addScalar("product", Hibernate.STRING).addScalar("slotNo", Hibernate.STRING).addScalar("reasonCode", Hibernate.STRING)
        .addScalar("t_thickness", Hibernate.STRING).addScalar("c_thickness", Hibernate.STRING)
        .addScalar("jbproduct", Hibernate.STRING).addScalar("dmproduct", Hibernate.STRING)
        .addScalar("cutproduct", Hibernate.STRING).addScalar("realboxid", Hibernate.STRING)
        .setResultTransformer(Transformers.aliasToBean(DatEntityMth.class));

      if (q.list().size() > 0) {
        list = q.list();
        ((DatEntityMth)list.get(0)).setQty(Integer.parseInt(count(sql2)));
        releaseSession(getSession());
        return list;
      }
      return null;
    } catch (Exception e) {
      e.printStackTrace();
      releaseSession(getSession());
    }
    return null;
  }

  public String count(StringBuffer condition) {
    StringBuffer sql = new StringBuffer("select count(*) ");
    sql.append(condition);
    return getSession().createSQLQuery(sql.toString()).uniqueResult().toString();
  }

  public int getFullQty(String id) {
    int i = 20;
    StringBuffer sql = new StringBuffer(
      "select BOX_STD_QTY as fullQty from RET_PRD_IN where MTRL_BOX_ID='" + id + "'");
    try {
      Query q = getSession().createSQLQuery(sql.toString()).addScalar("fullQty", Hibernate.INTEGER);

      if (q.list().size() > 0) {
        i = ((Integer)q.list().get(0)).intValue();
        releaseSession(getSession());
        return i;
      }
      return i;
    } catch (Exception e) {
      e.printStackTrace();
      releaseSession(getSession());
    }
    return i;
  }
}